package com.thh.common.utils.sql;

import com.thh.common.exception.UtilException;
import com.thh.common.utils.StringUtils;

import java.util.HashSet;
import java.util.Locale;
import java.util.Set;
import java.util.regex.Pattern;

/**
 * sql操作工具类
 *
 * @author thh
 */
public class SqlUtil {
    /**
     * 定义常用的 sql关键字
     */
    public static String SQL_REGEX = "select |insert |delete |update |drop |count |exec |chr |mid |master |truncate |char |and |declare ";

    /**
     * 仅支持字母、数字、下划线、空格、逗号、小数点（支持多个字段排序）
     */
    public static String SQL_PATTERN = "[a-zA-Z0-9_\\ \\,\\.]+";

    /**
     * 检查字符，防止注入绕过
     */
    public static String escapeOrderBySql(String value) {
        if (StringUtils.isNotEmpty(value) && !isValidOrderBySql(value)) {
            throw new UtilException("参数不符合规范，不能进行查询");
        }
        return value;
    }

    /**
     * 验证 order by 语法是否符合规范
     */
    public static boolean isValidOrderBySql(String value) {
        return value.matches(SQL_PATTERN);
    }

    /**
     * SQL关键字检查
     */
    public static void filterKeyword(String value) {
        if (StringUtils.isEmpty(value)) {
            return;
        }
        String[] sqlKeywords = StringUtils.split(SQL_REGEX, "\\|");
        for (String sqlKeyword : sqlKeywords) {
            if (StringUtils.indexOfIgnoreCase(value, sqlKeyword) > -1) {
                throw new UtilException("参数存在SQL注入风险");
            }
        }
    }

    /**
     * 去掉注释
     */
    public static String removeComments(String sql) {
        if (StringUtils.isBlank(sql)) {
            return sql;
        }
        Pattern p = Pattern.compile("(?ms)('(?:''|[^'])*')|--.*?$|/\\*.*?\\*/|#.*?$|");
        sql = p.matcher(sql).replaceAll("$1");
        return sql;
    }



    private static String startKeyword = "select|insert|delete|update|drop|exec|truncate|alter|create|show|explain";

    /**
     * 去掉注释
     */
    public static String removeComments(String sql, boolean isStripWhilte) {
        if (StringUtils.isBlank(sql)) {
            return sql;
        }
        sql = removeComments(sql);
        if (!isStripWhilte) {
            return sql;
        }
        String[] sqlList = sql.split(";");
        StringBuilder sb = new StringBuilder();
        for (String sqlData : sqlList) {
            String[] startKeywordArr = StringUtils.split(startKeyword, "\\|");
            for (String word : startKeywordArr) {
                String newSql = sqlData.toLowerCase();
                boolean contains = newSql.contains(word);
                if (contains) {
                // int index = newSql.indexOf(word);
                //sb.append(sqlData.substring(index));
                    sqlData = sqlData.trim().replaceAll("\r", "").replaceAll("\n", " ");
                    sb.append(sqlData);
                    sb.append(";");
                    break;
                }
            }
        }
        return sb.toString();
    }


    private static String specialQueryKeyword = "show|explain";

    public static boolean isSpecialQuerySql(String sql) {
        boolean flag = false;
        String[] specialQueryKeywordArr = StringUtils.split(specialQueryKeyword, "\\|");
        for (String word : specialQueryKeywordArr) {
            String newSql = sql.toLowerCase();
            boolean contains = newSql.contains(word);
            if (contains) {
                flag = true;
                break;
            }
        }
        return flag;

    }

    public static void main(String[] args) {

        String sql = removeComments("-- 啊飒飒十三点\n" +
                "select * from where a = '//*--123131313131313*\r\n/';\n" +
                "\n" +
                "//\n" +
                "/***/");
        sql = sql.trim().replaceAll("\r", "").replaceAll("\n", "");
        System.out.println("sql = " + sql);


    }
}
